using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Chatu.ControlLider.Web.Models.Common;
using Chatu.ControlLider.Web.Models.Domain;

namespace Chatu.ControlLider.Web.Models.Repositories
{
    public class SqlEmployeeRepository : IEmployeeRepository
    {
        public readonly DateTime _currentTimeZone;

        public SqlEmployeeRepository()
        {
            _currentTimeZone = DateTime.UtcNow.Subtract(new TimeSpan(4, 30, 0));
        }

        public DateTime CurrentTimeZone
        {
            get { return _currentTimeZone; }
        }

        public IList<EmployeeSummary> GetEmployees()
        {
            IList<EmployeeSummary> list = new List<EmployeeSummary>();

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_GetEmployeesSummary";

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                list.Add(new EmployeeSummary
                                             {
                                                 Cedula = reader.GetString(0),
                                                 FirstName = reader.GetString(1),
                                                 LastName = reader.GetString(2),
                                                 JobTitle = reader.GetString(3),
                                                 Type = reader.GetString(4)
                                             });
                            }
                        }
                    }
                }
            }

            return list;
        }

        public bool InsertEmployee(Employee employee)
        {
            bool success = false;

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_InsertEmployee";

                    cmd.Parameters.AddWithValue("@userID", employee.UserID);
                    cmd.Parameters.AddWithValue("@cedula", employee.Cedula);
                    cmd.Parameters.AddWithValue("@firstName", employee.FirstName);
                    cmd.Parameters.AddWithValue("@lastName", employee.LastName);
                    cmd.Parameters.AddWithValue("@title", employee.JobTitle);
                    cmd.Parameters.AddWithValue("@address", employee.Address);
                    cmd.Parameters.AddWithValue("@phone", employee.Phone);
                    cmd.Parameters.AddWithValue("@birthDate", employee.BirthDate.Value);
                    cmd.Parameters.AddWithValue("@hiredate", employee.HireDate.Value);
                    cmd.Parameters.AddWithValue("@type", employee.TypeID);
                    cmd.Parameters.AddWithValue("@education", employee.EducationLevelID);

                    conn.Open();

                    try
                    {
                        success = cmd.ExecuteNonQuery() == 1;
                    }
                    catch (SqlException ex)
                    {
                        if (ex.Number == 2627)
                            throw new DuplicateEmployeeException("Cedula");  
                    }
                }
            }

            return success;
        }

        public Employee GetEmployee(string cedula)
        {
            Employee employee = null;

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_GetEmployeeByCedula";
                    cmd.Parameters.AddWithValue("@cedula", cedula);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader != null && reader.Read())
                        {
                            employee = new Employee
                                           {
                                               UserID = reader.GetGuid(0),
                                               Cedula = reader.GetString(1),
                                               FirstName = reader.GetString(2),
                                               LastName = reader.GetString(3),
                                               JobTitle = reader.GetString(4),
                                               Address = reader.GetString(5),
                                               Phone = reader.GetString(6),
                                               BirthDate = reader.GetDateTime(7),
                                               HireDate = reader.GetDateTime(8),
                                               TypeID = reader.GetInt32(9),
                                               EducationLevelID = reader.GetInt32(10)
                                           };
                        }
                    }
                }
            }

            return employee;
        }

        public bool UpdateEmployee(Employee employee)
        {
            bool success;

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_UpdateEmployee";

                    cmd.Parameters.AddWithValue("@cedula", employee.Cedula);
                    cmd.Parameters.AddWithValue("@firstName", employee.FirstName);
                    cmd.Parameters.AddWithValue("@lastName", employee.LastName);
                    cmd.Parameters.AddWithValue("@title", employee.JobTitle);
                    cmd.Parameters.AddWithValue("@address", employee.Address);
                    cmd.Parameters.AddWithValue("@phone", employee.Phone);
                    cmd.Parameters.AddWithValue("@birthDate", employee.BirthDate.Value);
                    cmd.Parameters.AddWithValue("@hiredate", employee.HireDate.Value);
                    cmd.Parameters.AddWithValue("@type", employee.TypeID);
                    cmd.Parameters.AddWithValue("@education", employee.EducationLevelID);

                    conn.Open();

                    success = cmd.ExecuteNonQuery() == 1;
                }
            }

            return success;
        }

        public string GetEmployeeUserName(string cedula)
        {
            string userName;

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_GetEmployeeUserName";
                    cmd.Parameters.AddWithValue("@cedula", cedula);

                    conn.Open();

                    userName = cmd.ExecuteScalar().ToString();
                }
            }

            return userName;
        }

        public EmployeeLaborDay GetEmployeeLaborDay(string userName)
        {
            EmployeeLaborDay emp = null;
            
            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_GetEmployeeLaborDay";
                    cmd.Parameters.AddWithValue("@userName", userName);
                    cmd.Parameters.AddWithValue("@inDate", _currentTimeZone);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader != null && reader.Read())
                        {
                            emp = CreateEmployeeLaborDay(reader);
                        }
                    }
                }
            }

            return emp;
        }

        public bool RegiterEntrance(string cedula, DateTime inDate)
        {
            bool success;

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_InsertEmployeeAttendance";

                    cmd.Parameters.AddWithValue("@cedula", cedula);
                    cmd.Parameters.AddWithValue("@inDate", inDate);

                    conn.Open();

                    success = cmd.ExecuteNonQuery() == 1;
                }
            }

            return success;
        }

        public bool RegisterExit(string cedula, DateTime outDate, double guardia, double bono, int ticket)
        {
            bool success;

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_UpdateEmployeeAttendance";

                    cmd.Parameters.AddWithValue("@cedula", cedula);
                    cmd.Parameters.AddWithValue("@outDate", outDate);
                    cmd.Parameters.AddWithValue("@guardia", guardia);
                    cmd.Parameters.AddWithValue("@bono", bono);
                    cmd.Parameters.AddWithValue("@ticket", ticket);

                    conn.Open();

                    success = cmd.ExecuteNonQuery() == 1;
                }
            }

            return success;
        }

        public IList<EmployeeLaborDay> GetEmployeesLaborData()
        {
            IList<EmployeeLaborDay> list = new List<EmployeeLaborDay>();

            using (SqlConnection conn = new SqlConnection(DataSession.ConnectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Lider.usp_Employee_GetEmployeesLaborData";

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                list.Add(CreateEmployeeLaborDay(reader));
                            }
                        }
                    }
                }
            }

            return list;
        }

        #region Helper Methods

        private EmployeeLaborDay CreateEmployeeLaborDay(SqlDataReader reader)
        {
            EmployeeLaborDay emp = new EmployeeLaborDay
                                       {
                                           Cedula = reader.GetString(0),
                                           FirstName = reader.GetString(1),
                                           LastName = reader.GetString(2),
                                           JobTitle = reader.GetString(3)
                                       };

            if (reader[4] != DBNull.Value)
                emp.LaborData.InDate = reader.GetDateTime(4);

            if (reader[5] != DBNull.Value)
                emp.LaborData.OutDate = reader.GetDateTime(5);

            if (reader[6] != DBNull.Value)
                emp.LaborData.Guardia = reader.GetDouble(6);

            if (reader[7] != DBNull.Value)
                emp.LaborData.Bono = reader.GetDouble(7);

            if (reader[8] != DBNull.Value)
                emp.LaborData.Ticket = reader.GetInt32(8);

            return emp;
        }

        #endregion
    }
}